** Alternative Capability Rankings (Total trade)
** This do-file is necessary for conducting robustness check shown in Table A24 

/* input 
${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta
${output_data}quota_mex_weight_hs6.dta
*/

/* output
${output_data}mexico_for_analysis_04_07_hs6_restrict_total.dta
${output_data}US_for_analysis_04_07_hs6_restrict_total.dta
*/


*************************
*Step 1 Create importer rank by hs6
*************************

* Create data for making rankings in 2004
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", clear
keep rfc hs6 year newclid2 value_exporter_importer
keep if year==2004
label var newclid2 "importer id"
label var value_exporter_importer "exports of hs6 between exporter and importer"
label var rfc "exporter id"
label var hs6 "hs6"
collapse (sum) value_exporter_importer, by(rfc newclid2 hs6)
tostring newclid2, generate(importer_id)
gen hs6_importer_id=hs6+"_"+ importer_id
gen hs6_exporter_id=hs6+"_"+ rfc
save "${temp_data_total}data_for_ranking_hs6.dta", replace

* Create ranking of match values in 2004 (base year)
use "${temp_data_total}data_for_ranking_hs6.dta", clear
collapse (sum) value_exporter_importer, by(hs6_importer_id)
gen hs6=substr(hs6_importer_id,1,6)
gsort hs6 -value_exporter_importer
by hs6: gen mv_rank=_n
label var mv_rank "ranking of match values in hs6 for importer"
tostring mv_rank, generate(mv_rank_s)
gen hs6_importer_id_mv_rank= hs6_importer_id+"_"+ mv_rank_s
drop hs6
sort hs6_importer_id
save "${temp_data_total}importer_ranking_hs6.dta", replace


*************************
*Step 2 Create Mexican downgrading 6-digit level data
*************************

* Create data on export values between importers and exporters
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", clear
keep rfc hs6 year newclid2 value_exporter_importer
collapse (sum) value_exporter_importer, by(rfc newclid2 year hs6)
gen hs6_exporter_id= hs6+"_"+ rfc
tostring newclid2, generate(importer_id)
gen hs6_importer_id=hs6+"_"+ importer_id
save "${temp_data_total}exporter_importer_hs6_restrict.dta", replace

* Create data for rankings 
use "${temp_data_total}exporter_importer_hs6_restrict.dta", clear
tostring year, generate(year_s)
gen hs_exporter_id_year= hs6_exporter_id+"_"+year_s
gsort hs_exporter_id_year -value_exporter_importer
by hs_exporter_id_year: gen importer_rank=_n
save "${temp_data_total}down_grading_hs6.dta", replace

* Identify main partner in 2004 2007
foreach j in 2004 2007 {
use "${temp_data_total}down_grading_hs6.dta", clear 
keep if importer_rank==1 
keep if year==`j'
keep hs6_exporter_id  hs6_importer_id
sort hs6_importer_id
merge m:1 hs6_importer_id using "${temp_data_total}importer_ranking_hs6.dta"
drop if _merge!=3
rename hs6_importer_id main`j'_id
rename mv_rank mv_rank`j'
keep hs6_exporter_id main`j'_id mv_rank`j' 
save "${temp_data_total}main`j'_hs6.dta", replace
}

* Create data on 2004-2007 for Mexico 
use "${temp_data_total}down_grading_hs6.dta", clear 
keep if year==2004
keep hs6 rfc hs6_exporter_id
duplicates drop
merge m:1 hs6_exporter_id using "${temp_data_total}main2004_hs6.dta"
drop if _merge==2
drop _merge
merge m:1 hs6_exporter_id using "${temp_data_total}main2007_hs6.dta"
drop if _merge==2
drop _merge
sort hs6
* Identify whether the ranking was downgrading
gen mv_rank_d0704= mv_rank2007 -mv_rank2004
label var mv_rank_d0704 "mv_rank_2007-mv_rank_2004"
gen i_mv_rank_d0704=1 if mv_rank_d0704>0
replace  i_mv_rank_d0704=0 if mv_rank_d0704<=0
replace  i_mv_rank_d0704=. if mv_rank_d0704==.
* Create data for main analysis (Mexico)
keep hs6 main2004_id hs6_exporter_id mv_rank_d0704 mv_rank2004 mv_rank2007 i_mv_rank_d0704
gen newclid2=substr(main2004_id, 8, length(main2004_id))
destring newclid2, replace
sort hs6
* Merge with quota data (for making a binding indicator)
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
drop _merge

gen hs2=substr(hs6,1,2)
save "${output_data}mexico_for_analysis_04_07_hs6_restrict_total.dta", replace


*************************
*Step 3 Create exporter rank by hs6
*************************

use "${temp_data_total}data_for_ranking_hs6.dta", replace
collapse (sum) value_exporter_importer, by(hs6_exporter_id)
gen hs6=substr(hs6_exporter_id,1,6)
gsort hs6 -value_exporter_importer
by hs6: gen e_mv_rank=_n
keep hs6_exporter_id e_mv_rank
tostring e_mv_rank, generate(e_mv_rank_s)
gen hs6_exporter_id_e_mv_rank= hs6_exporter_id+"_"+ e_mv_rank_s
keep hs6_exporter_id hs6_exporter_id_e_mv_rank e_mv_rank
sort hs6_exporter_id_e_mv_rank		
gen e_mv_partner=hs6_exporter_id
label var e_mv_partner "id of exporter's main importer"
save "${temp_data_total}exporter_ranking_hs6.dta", replace


*************************
*Step 4 Create US upgrading 6-digit level data
*************************

* Create data for rankings 
use "${temp_data_total}exporter_importer_hs6_restrict.dta", clear
tostring year, generate(year_s)
gen hs_importer_id_year= hs6_importer_id+"_"+year_s
gsort hs_importer_id_year -value_exporter_importer
by hs_importer_id_year: gen exporter_rank=_n
save "${temp_data_total}up_grading_hs6.dta", replace

* Identify main partner in 2004 2007
foreach j in 2004 2007 {
use "${temp_data_total}up_grading_hs6.dta", clear 
keep if exporter_rank==1 
keep if year==`j'
keep hs6_exporter_id  hs6_importer_id
sort hs6_exporter_id
merge m:1 hs6_exporter_id using "${temp_data_total}exporter_ranking_hs6.dta"
drop if _merge!=3
rename hs6_exporter_id main`j'_id
rename e_mv_rank e_mv_rank`j'
keep hs6_importer_id main`j'_id e_mv_rank`j'
save "${temp_data_total}main`j'_exp_hs6.dta", replace
}

* Create data on 2004-2007 for US
use "${temp_data_total}up_grading_hs6.dta", clear 
keep if year==2004
keep hs6 importer_id  hs6_importer_id
duplicates drop
sort hs6_importer_id
merge m:1 hs6_importer_id using "${temp_data_total}main2004_exp_hs6.dta"
drop if _merge==2
drop _merge
sort hs6_importer_id
merge m:1 hs6_importer_id using "${temp_data_total}main2007_exp_hs6.dta"
drop if _merge==2
drop _merge
sort hs6
* Identify whether the ranking was upgrading
gen e_mv_rank_d0704= e_mv_rank2007 -e_mv_rank2004
gen i_e_mv_rank_d0704=1 if e_mv_rank_d0704<0
replace  i_e_mv_rank_d0704=0 if e_mv_rank_d0704>=0
replace  i_e_mv_rank_d0704=. if e_mv_rank_d0704==.
label var e_mv_rank_d0704 "e_mv_rank_2007-e_mv_rank_2004"
label var i_e_mv_rank_d0704 "1 if e_mv_rank_d0704<0 (upgrade) and 0 otherwise"
* Create data for main analysis (US)
keep hs6 main2004_id hs6_importer_id e_mv_rank_d0704 i_e_mv_rank_d0704 e_mv_rank2004 e_mv_rank2007 
gen newclid2=substr(hs6_importer_id, 8, length(main2004_id))
destring newclid2, replace
sort hs6
* Merge with quota data (for making a binding indicator)
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
drop _merge

gen hs2=substr(hs6,1,2)

save "${output_data}US_for_analysis_04_07_hs6_restrict_total.dta", replace
